﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using HRM.DAL.DAO;

namespace HRM.DAL.Gateway
{
    public class AttendanceGateway : DBGateway
    {
        public bool SaveAttendance(string path)
        {
            try
            {
               
                string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties='Excel 8.0;'";

                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                OleDbCommand cmd = new OleDbCommand("Select [Id],[Code],[Date],[InTime],[OutTime],[Duration],[Comments] from [Sheet1$]", excelConnection);
                excelConnection.Open();
                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();
                ConnectionObj.Open();
                SqlBulkCopy sqlBulk = new SqlBulkCopy(ConnectionObj);
                sqlBulk.DestinationTableName = "tblAttendance";
                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured into Database, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool UpdateStatus(int id, string status)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblAttendance set Status=@status where Id='"+id+"'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Updating Status, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool AddComment(int id, string comment)
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("update tblAttendance set Comments=@comment where Id='" + id + "'");
                CommandObj.Parameters.Clear();
                CommandObj.Parameters.Add("@comment", SqlDbType.VarChar).Value = comment;
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Add Comments, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public bool ClearAttendanceInfo()
        {
            try
            {
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format("Delete from tblAttendance");
                CommandObj.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured While Clearing Information, Try again !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }

        public List<Attendance> GetEmployeeAttendanceInfo(Code code)
        {
            try
            {
                List<Attendance> attendances=new List<Attendance>();
                ConnectionObj.Open();
                CommandObj.CommandText = string.Format(@"select * from tblAttendance where Code='"+code.CodeName+"'");
                SqlDataReader reader = CommandObj.ExecuteReader();
                while (reader.Read())
                {
                    Attendance attendance=new Attendance();
                    attendance.Id = Convert.ToInt32(reader[0]);
                    Code aCode=new Code();
                    aCode.CodeName = reader[1].ToString();
                    attendance.Code = aCode;
                    DateTime dt = Convert.ToDateTime(reader[2]);
                    attendance.Date = dt.ToShortDateString();

                    if (reader[3].ToString() != "")
                    {
                        DateTime indt = Convert.ToDateTime(reader[3]);
                        attendance.InTime = indt.ToShortTimeString();
                    }
                    else
                    {
                        attendance.InTime = "";
                    }

                    if (reader[4].ToString() != "")
                    {
                        DateTime outdt = Convert.ToDateTime(reader[4]);
                        attendance.OutTime = outdt.ToShortTimeString();
                    }
                    else
                    {
                        attendance.OutTime = "";
                    }

                    if (reader[5].ToString() != "")
                    {
                        DateTime dura = Convert.ToDateTime(reader[5]);
                        attendance.Duration = dura.ToShortTimeString();
                    }
                    else
                    {
                        attendance.Duration = "";
                    }
                    
                    attendance.Comments = reader[6].ToString();
                    attendance.Status = reader[7].ToString();
                    attendances.Add(attendance);
                }
                return attendances;
            }
            catch (Exception)
            {
                throw new Exception("Error Occured During Retriving Employee Attendance Information !");
            }
            finally
            {
                ConnectionObj.Close();
            }
        }
    }
}